unit PPS_View;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Setting, Grids, StdCtrls, ZAbstractTable, ZDataset, DB,
  ZAbstractRODataset, ZAbstractDataset, ZConnection, PPS_Tambah, PPS_Edit,
  frxpngimage, ExtCtrls;

type
  TPPS_View_Form = class(TForm)
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Button1: TButton;
    Button2: TButton;
    Button3: TButton;
    Button4: TButton;
    Button5: TButton;
    kabupatencombo: TComboBox;
    kecamatancombo: TComboBox;
    desacombo: TComboBox;
    table: TStringGrid;
    ZConnection1: TZConnection;
    ZQuery1: TZQuery;
    ZTable1: TZTable;
    Image1: TImage;
    Image2: TImage;
    Memo1: TMemo;
    procedure FormCreate(Sender: TObject);
    procedure kabupatencomboChange(Sender: TObject);
    procedure kecamatancomboChange(Sender: TObject);
    procedure desacomboChange(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure tampilpetugas();
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure Button5Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
    procedure refresh();
  private
    procedure AutoSizeCol(Grid: TStringGrid; Column: integer);
    { Private declarations }
  public
    { Public declarations }
    constructor Create(AOwner: TComponent; kode_login: String);             overload;
  end;

var
  PPS_View_Form: TPPS_View_Form;
  slogin,sdesa, skec, skota, textdesa,textkec, textkota:string;
  listid:tstringlist;

implementation

{$R *.dfm}

constructor TPPS_View_Form.Create(AOwner: TComponent; kode_login: String);
begin
    slogin:=kode_login;
    inherited Create(AOwner);
end;
procedure TPPS_View_Form.Button1Click(Sender: TObject);
begin
  refresh;
end;

procedure TPPS_View_Form.refresh;
var
  query:string;
  i:Integer;
begin
    if (desacombo.ItemIndex > 0) then begin
    with Zquery1 do
    begin
      active:=false;
      query := 'SELECT a.ID_PETUGAS as ID_PETUGAS, a.POSISI as POSISI, a.NAMA as NAMA, a.JABATAN as JABATAN, b.TAMPIL AS DES, c.TAMPIL AS KEC, d.TAMPIL as KOT ';
      query := query + ' FROM ((petugas a LEFT JOIN desa b ON a.KODE_DESA = b.KODE_DESA)';
      query := query + ' LEFT JOIN kecamatan c ON a.KODE_KECAMATAN = c.KODE_KECAMATAN)';
      query := query + ' LEFT JOIN kabkot d ON a.KODE_KABKOT = d.KODE ';
      memo1.Text := query;
      query:= query + ' WHERE a.KODE_DESA='+QuotedStr( sdesa)+'  ORDER BY a.KODE_DESA ASC';
      SQL.clear;
      SQL.text:=query;
      active:=true;
      first;
      tampilpetugas;
    end;

  end
  else if (kecamatancombo.ItemIndex > 0) then begin
    with Zquery1 do
    begin
      active:=false;
      query := 'SELECT a.ID_PETUGAS as ID_PETUGAS, a.POSISI as POSISI, a.NAMA as NAMA, a.JABATAN as JABATAN, b.TAMPIL AS DES, c.TAMPIL AS KEC, d.TAMPIL as KOT ';
      query := query + ' FROM ((petugas a LEFT JOIN desa b ON a.KODE_DESA = b.KODE_DESA)';
      query := query + ' LEFT JOIN kecamatan c ON a.KODE_KECAMATAN = c.KODE_KECAMATAN)';
      query := query + ' LEFT JOIN kabkot d ON a.KODE_KABKOT = d.KODE';
      memo1.Text := query;
      query:= query + ' WHERE a.KODE_KECAMATAN='+QuotedStr( skec)+' ORDER BY a.KODE_KECAMATAN, a.KODE_DESA ASC';
      SQL.clear;
      SQL.text:=query;
      active:=true;
      first;
      tampilpetugas;
    end;
  end
  else if (kabupatencombo.ItemIndex > 0) then begin
    with Zquery1 do
    begin
      active:=false;
      query := 'SELECT a.ID_PETUGAS as ID_PETUGAS, a.POSISI as POSISI, a.NAMA as NAMA, a.JABATAN as JABATAN, b.TAMPIL AS DES, c.TAMPIL AS KEC, d.TAMPIL as KOT ';
      query := query + ' FROM ((petugas a LEFT JOIN desa b ON a.KODE_DESA = b.KODE_DESA)';
      query := query + ' LEFT JOIN kecamatan c ON a.KODE_KECAMATAN = c.KODE_KECAMATAN)';
      query := query + ' LEFT JOIN kabkot d ON a.KODE_KABKOT = d.KODE';
      memo1.Text := query;
      query:= query + ' WHERE a.KODE_KABKOT='+QuotedStr( skota)+' ORDER BY a.KODE_KABKOT, a.KODE_KECAMATAN, a.KODE_DESA ASC';
      SQL.clear;
      SQL.text:=query;
      active:=true;
      first;
      tampilpetugas;
    end;
  end
  else
  begin
    with ZTable1 do
    begin
      active:=false;
      tablename:='petugas';
      filter:='POSISI=1';
      filtered:=true;
      active:=true;
      tampilpetugas;
    end;
  end;
  for i := 0 to table.ColCount - 1 do
      AutoSizeCol(table, i);
end;

procedure TPPS_View_Form.tampilpetugas();
var counter:integer;
begin
  counter:=1;
  with zquery1 do
  begin
    table.RowCount:=RecordCount+1;
    table.ColCount:=7;
    table.Cells[0,0]:='No';
    table.Cells[1,0]:='Nama';
    table.Cells[2,0]:='Posisi';
    table.Cells[3,0]:='Kota/Kabupaten';
    table.Cells[4,0]:='Kecamatan';
    table.Cells[5,0]:='Kelurahan/Desa';
    table.Cells[6,0]:='Jabatan';

    first;

    listid:=tstringlist.Create;
    listid.Clear;

    while (not eof) do
    begin
      listid.Add(fields[0].AsString);
      //showmessage('0');
      table.Cells[0,counter]:=inttostr(counter);
      //showmessage('1');
      table.Cells[1,counter]:=fields[2].AsString;
      //showmessage('2');
      if(fields[1].AsString='1')then
        table.Cells[2,counter]:='KPU Prov'
      else if(fields[1].AsString='2')then
        table.Cells[2,counter]:='KPU Kab/Kot'
      else if(fields[1].AsString='3')then
        table.Cells[2,counter]:='PPK'
      else if(fields[1].AsString='4')then
        table.Cells[2,counter]:='PPS';
      //  showmessage('3');
      table.Cells[3,counter]:=fields[6].AsString;
      //showmessage('4');
      table.Cells[4,counter]:=fields[5].AsString;
      //showmessage('5');
      table.Cells[5,counter]:=fields[4].AsString;
      //showmessage('6');
      if(fields[3].AsString='1')then
        table.Cells[6,counter]:='Ketua'
      else if(fields[3].AsString='2')then
        table.Cells[6,counter]:='Anggota';
      //  showmessage('7');
      counter:=counter+1;
      next;
    end;
  end;
end;
procedure TPPS_View_Form.Button2Click(Sender: TObject);
var tambah:TPPS_Form;
begin
  tambah:=TPPS_Form.Create(application, slogin);
end;

procedure TPPS_View_Form.AutoSizeCol(Grid: TStringGrid; Column: integer);
var
  i, W, WMax: integer;
begin
  WMax := 0;
  for i := 0 to (Grid.RowCount - 1) do begin
    W := Grid.Canvas.TextWidth(Grid.Cells[Column, i]);
    if W > WMax then
      WMax := W;
  end;
  Grid.ColWidths[Column] := WMax + 5;
end;

procedure TPPS_View_Form.Button3Click(Sender: TObject);
var edit:TPPS_Edit_Form;
begin
  if((table.Row>0)and(table.Row<=listid.Count))then
  begin
    edit:=TPPS_Edit_Form.Create(application, table.Cells[2,table.Row], table.Cells[3,table.Row], table.Cells[4,table.Row], table.Cells[5,table.Row], table.Cells[1,table.Row], table.Cells[6,table.Row],listid[table.Row-1]);
  end;
end;

procedure TPPS_View_Form.Button4Click(Sender: TObject);
begin
  if((table.Row>0)and(table.Row<=listid.Count))then
  begin
    if(MessageDlg('Apakah Anda yakin akan menghapus petugas tersebut?',mtError, mbOKCancel, 0)=mrOk)then
    begin
      with ZQuery1 do
      begin
        active:=false;
        sql.Clear;
        sql.Text:='delete from petugas where ID_PETUGAS='+QuotedStr(listid[table.Row-1]);
        ExecSQL;
      end;
      showmessage('data telah berhasil dihapus');
      refresh;
    end;
  end;
end;

procedure TPPS_View_Form.Button5Click(Sender: TObject);
begin
  Close;
end;

procedure TPPS_View_Form.desacomboChange(Sender: TObject);
var
  desa:String;
  query:string;
  kode:string;
begin
  if (desacombo.ItemIndex > 0) then begin
    desa := desacombo.Items[desacombo.ItemIndex];
    textdesa:=desa;
    ZTable1.Active := False;
    ZTable1.TableName := 'desa';
    ZTable1.Filter := 'TAMPIL = '+QuotedStr(desa)+' AND KODE_KECAMATAN = '+QuotedStr(skec);
    ZTable1.Filtered := True;
    ZTable1.Active := True;
    ZTable1.First;
    sDesa := ZTable1.FieldByName('KODE_DESA').AsString;
  end
    else
  begin
    sDesa:='';
    textdesa:='';
  end;
end;

procedure TPPS_View_Form.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  Action:=caFree;
end;

procedure TPPS_View_Form.FormCreate(Sender: TObject);
var setting:TSetting_Form;
kkota, kkec : string;
begin
  setting:=TSetting_Form.Create;
  ZConnection1.HostName:=setting.host;
  ZConnection1.Protocol:=setting.protocol;
  ZConnection1.Database:=setting.database;
  ZConnection1.User:=setting.user;
  ZConnection1.Password:=setting.password;
  ZConnection1.Connected:=true;
  ztable1.Connection:=ZConnection1;
  ZQuery1.Connection:=ZConnection1;
  ZConnection1.LoginPrompt:=False;
  ZConnection1.Connected := True;

  BorderIcons := BorderIcons - [biMaximize];
  skota:='';
  sKec:='';
  sDesa:='';
  textkota:='';
  textKec:='';
  textDesa:='';

  desacombo.items.Add('Semua Kel/Desa');
  //jabatancombo.items.Add('Pilih Jabatan');
  //posisicombo.Items.Add('Pilih Posisi');
  kabupatencombo.Items.Add('Semua Kab/Kota');
  Kecamatancombo.Items.Add('Semua Kecamatan');
  desacombo.ItemIndex:=0;
  kecamatancombo.ItemIndex:=0;
  kabupatencombo.ItemIndex:=0;
  //showmessage(slogin);
  ZTable1.Active := False;
  ZTable1.TableName := 'kabkot';
  if (slogin = '00') then
  begin
    ZTable1.Active := True;
    ZTable1.First;
    while (not ZTable1.Eof) do
    begin
      kabupatencombo.Items.Add(ZTable1.FieldByName('TAMPIL').AsString);
      ZTable1.Next;
    end;
  end
  else if(Length(slogin) = 2) then
  begin
    ZTable1.filter:='KODE='+quotedstr(slogin);
    ZTable1.filtered:=true;
    ZTable1.Active := True;
    ZTable1.First;
    skota:=slogin;
    textkota:=slogin;
    kabupatencombo.Items.Add(ZTable1.FieldByName('TAMPIL').AsString);
    kabupatencombo.enabled:=false;
    kabupatencombo.ItemIndex:=1;
    ZTable1.Active := false;
    ZTable1.tablename:='kecamatan';
    ztable1.filter:='KODE_KABKOT='+quotedstr(slogin);
    ztable1.filtered:=true;
    ZTable1.active:=true;
    ZTable1.first;
    while(not ZTable1.eof)do
    begin
      kecamatancombo.Items.Add(ztable1.fieldbyname('TAMPIL').asstring);
      ZTable1.next;
    end;

  end
  else
  begin
    kkota := copy(slogin, 1, 2);
    kkec := slogin;
    ZTable1.filter:='KODE='+quotedstr(kkota);
    ZTable1.filtered:=true;
    ZTable1.Active := True;
    ZTable1.First;
    skota:=kkota;
    textkota:=slogin;
    skec := slogin;
    kabupatencombo.Items.Add(ZTable1.FieldByName('TAMPIL').AsString);
    kabupatencombo.enabled:=false;
    kabupatencombo.ItemIndex:=1;

    ZTable1.Active := false;
    ZTable1.tablename:='kecamatan';
    ztable1.filter:='KODE_KECAMATAN='+quotedstr(slogin);
    ztable1.filtered:=true;
    ZTable1.active:=true;
    ZTable1.first;
    kecamatancombo.Items.Add(ztable1.fieldbyname('TAMPIL').asstring);
    kecamatancombo.enabled:=false;
    kecamatancombo.itemindex := 1;

    ZTable1.Active := false;
    ZTable1.tablename:='desa';
    ztable1.filter:='KODE_KECAMATAN='+quotedstr(slogin);
    ztable1.filtered:=true;
    ZTable1.active:=true;
    ZTable1.first;
    while(not ZTable1.eof)do
    begin
      desacombo.Items.Add(ztable1.fieldbyname('TAMPIL').asstring);
      ZTable1.next;
    end;

  end;
end;

procedure TPPS_View_Form.kabupatencomboChange(Sender: TObject);
var
  pilihan:String;
begin
  if (kabupatencombo.ItemIndex > 0) then
  begin
    pilihan := kabupatencombo.Items[kabupatencombo.ItemIndex];
    textkota:=pilihan;
    with ZTable1 do
      begin
        Active := False;
        TableName := 'kabkot';
        Filter := 'TAMPIL = '+QuotedStr(pilihan);
        Filtered := True;
        Active := True;
        First;
        skota := FieldByName('KODE').AsString;
        //cb2
        Active := False;
        TableName := 'kecamatan';
        Filter := 'KODE_KABKOT = '+QuotedStr(skota);
        Filtered := True;
        Active := True;
        First;
      end;
    kecamatancombo.Clear;
    kecamatancombo.Items.Add('PILIH KECAMATAN');
    kecamatancombo.ItemIndex:=0;
    desacombo.Clear;
    desacombo.Items.Add('PILIH KELURAHAN/DESA');
    desacombo.ItemIndex:=0;
    while (not ZTable1.Eof) do
      begin
        kecamatancombo.Items.Add(ZTable1.FieldByName('TAMPIL').AsString);
        ZTable1.Next;
      end;
  end
    else
  begin
    skota:='';
    textkota:='';
    kecamatancombo.Clear;
    desacombo.Clear;
  end;
  textkec:='';
  textdesa:='';
  sKec:='';
  sDesa:='';
end;

procedure TPPS_View_Form.kecamatancomboChange(Sender: TObject);
var
  pilihan:String;
begin
  if (kecamatancombo.ItemIndex > 0) then
  begin
    pilihan := kecamatancombo.Items[kecamatancombo.ItemIndex];
    textkec:=pilihan;
    ZTable1.Active := False;
    ZTable1.TableName := 'kecamatan';
    ZTable1.Filter := 'TAMPIL = '+QuotedStr(pilihan)+ ' and KODE_KABKOT = ' + QuotedStr
      (skota);
    ZTable1.Filtered := True;
    ZTable1.Active := True;
    ZTable1.First;
    sKec := ZTable1.FieldByName('KODE_KECAMATAN').AsString;
    //cb3
    ZTable1.Active := False;
    ZTable1.TableName := 'desa';
    ZTable1.Filter := 'KODE_KECAMATAN = '+QuotedStr(sKec);
    ZTable1.Filtered := True;
    ZTable1.Active := True;
    ZTable1.First;
    desacombo.Clear;
    desacombo.Items.Add('PILIH KELURAHAN/DESA');
    desacombo.ItemIndex:=0;
    while (not ZTable1.Eof) do
      begin
        desacombo.Items.Add(ZTable1.FieldByName('TAMPIL').AsString);
        ZTable1.Next;
      end;
  end
    else
  begin
    sKec:='';
    textkec:='';
    desacombo.Clear;
  end;
  textdesa:='';
  sDesa:='';
end;

end.
